package library.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import library.dao.IlibraryDao;
import library.dao.Impl.BookDaoImpl;
import library.dao.Impl.BorrowHistoryDao;
import library.dao.Impl.UserDaoImpl;
import library.db.DbConnection;
import library.pojo.Book;
import library.pojo.BorrowHistory;
import library.pojo.User;

/**
 * @author zaizai
 *
 */
public class LibraryService {
	private static IlibraryDao libraryDao = new BookDaoImpl();
	private static IlibraryDao borrowDao = new BorrowHistoryDao();
	private static IlibraryDao userDao = new UserDaoImpl();
	
	/**
	 * 添加图书信息
	 */
	public void addBook(Book book) {
		libraryDao.save(book);
	}

	/**
	 * 修改图书信息
	 */
	public void updateBook(Book book) {
		libraryDao.update(book);

	}

	/**
	 * 修改用户信息
	 */
	public void updateUser(User user) {
		userDao.updateUser(user);

	}
	
	/**
	 * 删除图书信息
	 */
	public void deleteBook(String isbn) {
		libraryDao.delete(isbn);

	}

	/**
	 * 删除用户信息
	 */
	public void deleteUser(String username) {
		userDao.deleteUser(username);

	}
	
	/**
	 * 查询图书信息
	 */
	public List<Book> getBook(String isbn) {
		return libraryDao.get(isbn);
	}

	/**
	 * 查询所有图书信息
	 */
	public static List<Book> findAllBook() {
		return libraryDao.findAll();

	}
	

	/**
	 * 查询所有借阅历史
	 */
	public static List<BorrowHistory> findAllBookBorrowHistory() {
		return borrowDao.findAllBorrowHistory();

	}
	
	
	/**
	 * 查询所有用户信息
	 */
	public static List<User> findAllUser() {
		return userDao.findAllUser();

	}
	
	/**
	 * 查询指定用户借阅历史
	 */
	public static List<BorrowHistory> findOneBorrowHistory(String user) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history where borrow_user = ?";
		BorrowHistory borrowHistory = null;
		List<BorrowHistory> borrowHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user);
			rs = ps.executeQuery();
			while (rs.next()) {
				String isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				borrowHistory = new BorrowHistory(isbn, bookName, borrowUser, borrowDate, borrowState, time, count);
				borrowHistoryList.add(borrowHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return borrowHistoryList;
	}
	
	
	/**
	 * 按用户类型查询用户信息
	 */
	public List<User> getUserType(String authorName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from user " + "where type like '%"+authorName+"%'";
		User user = null;
		List<User> userList = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String username = rs.getString(1);
				String password = rs.getString(2);
				String name = rs.getString(3);
				String phone = rs.getString(4);
				String type = rs.getString(5);
				user = new User(username, password, name,type,phone);
				userList.add(user);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return userList;
	}
	
	/**
	 * 按姓名查询用户信息
	 */
	public List<User> getName(String authorName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from user " + "where name like '%"+authorName+"%'";
		User user = null;
		List<User> userList = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String username = rs.getString(1);
				String password = rs.getString(2);
				String name = rs.getString(3);
				String phone = rs.getString(4);
				String type = rs.getString(5);
				user = new User(username, password, name,type,phone);
				userList.add(user);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return userList;
	}
	
	/**
	 * 按用户名查询用户信息
	 */
	public List<User> getUsername(String authorName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from user " + "where username like '%"+authorName+"%'";
		User user = null;
		List<User> userList = new ArrayList<User>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String username = rs.getString(1);
				String password = rs.getString(2);
				String name = rs.getString(3);
				String phone = rs.getString(4);
				String type = rs.getString(5);
				user = new User(username, password, name,type,phone);
				userList.add(user);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return userList;
	}
	
	/**
	 * 按作者名字查询书的信息
	 */
	public List<Book> getAuthorBook(String authorName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from book " + "where author like '%"+authorName+"%'";
		Book book = null;
		List<Book> bookList = new ArrayList<Book>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String bookIsbn = rs.getString(1);
				String bookName = rs.getString(2);
				String bookAuthor = rs.getString(3);
				String publicationYear = rs.getString(4);
				String bookTypeIdx = rs.getString(5);
				String bookState = rs.getString(6);
				book = new Book(bookIsbn, bookName, bookAuthor, publicationYear, bookTypeIdx, bookState);
				bookList.add(book);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookList;
	}
	
	/**
	 * 按状态查询书的信息
	 */
	public List<Book> getStateBook(String authorName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from book " + "where state like '%"+authorName+"%'";
		Book book = null;
		List<Book> bookList = new ArrayList<Book>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String bookIsbn = rs.getString(1);
				String bookName = rs.getString(2);
				String bookAuthor = rs.getString(3);
				String publicationYear = rs.getString(4);
				String bookTypeIdx = rs.getString(5);
				String bookState = rs.getString(6);
				book = new Book(bookIsbn, bookName, bookAuthor, publicationYear, bookTypeIdx, bookState);
				bookList.add(book);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookList;
	}
	
	/**
	 * 通过状态查询书的信息
	 */
	public List<BorrowHistory> getState(String isbn) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where borrow_state like '%"+isbn+"%'";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookName, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	/**
	 * 通过状态查询书的信息
	 */
	public List<BorrowHistory> getState(String isbn,String user) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where borrow_state like '%"+isbn+"%' and borrow_user = ?";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookName, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	/**
	 * 查询书的信息
	 */
	public List<BorrowHistory> getIsbn(String isbn) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where isbn like '%"+isbn+"%'";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookName, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	/**
	 * 查询书的信息
	 */
	public List<BorrowHistory> getIsbn(String isbn,String user) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where isbn like '%"+isbn+"%' and borrow_user = ?";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookName, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	public List<Book> getSearchIsbn(String isbn) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from book " + "where isbn like '%"+isbn+"%'";
		Book book = null;
		List<Book> bookList = new ArrayList<Book>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String author = rs.getString(3);
				String year = rs.getString(4);
				String idx = rs.getString(5);
				String state = rs.getString(6);
				book = new Book(Isbn, bookName, author,year,idx,state);
				bookList.add(book);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookList;
	}
	/**
	 * 查询书的信息
	 */
	public List<BorrowHistory> getbookName(String bookName,String user) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where book_name like '%"+bookName+"%' and borrow_user = ?";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, user);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookname = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookname, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	/**
	 * 查询书的信息
	 */
	public List<BorrowHistory> getbookName(String bookName) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where book_name like '%"+bookName+"%'";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookname = rs.getString(2);
				String borrowUser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookname, borrowUser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
	
	/**
	 * 查询书的信息
	 */
	public List<BorrowHistory> getBorrowUser(String borrowUser) {
		Connection conn = null;
		ResultSet rs = null;
		PreparedStatement ps = null;
		conn = DbConnection.getConnection();
		String sql = "select * from borrow_history " + "where borrow_user like '%"+borrowUser+"%'";
		BorrowHistory bookHistory = null;
		List<BorrowHistory> bookHistoryList = new ArrayList<BorrowHistory>();
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				String Isbn = rs.getString(1);
				String bookName = rs.getString(2);
				String borrowuser = rs.getString(3);
				String borrowDate = rs.getString(4);
				int time = rs.getInt(5);
				String borrowState = rs.getString(6);
				int count = rs.getInt(7);
				bookHistory = new BorrowHistory(Isbn, bookName, borrowuser, borrowDate, borrowState,time,count);
				bookHistoryList.add(bookHistory);
			}
		} catch (SQLException e) {
			System.out.println("Jdbc程序发生错误：" + e);
		} finally {
			DbConnection.close(ps, rs);
		}
		return bookHistoryList;
	}
}
